﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class frm_Add_F4SerialInput : Form
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private string error;
        private string error_planno;
        private string error_order;
        private string error_quantity;

        private string[] Alphabet;
        private string yearLetter;
        private string yearDigit;
        private string monthLetter;
        private string monthDigit;
        private string GHRYearLetter;
        private string firstLetter;
        private int serialDigit;
        private string serialLetter;

        public frm_Add_F4SerialInput()
        {
            InitializeComponent();

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.error = null;
            this.yearLetter = null;
            this.yearDigit = null;
            this.monthDigit = null;
            this.monthLetter = null;
            this.GHRYearLetter = null;
            this.firstLetter = null;
            this.serialDigit = 0;
            this.serialLetter = null;
            this.error_quantity = null;
            
            Alphabet = new string[26] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N",
                "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };

            LoadComp();
            if (string.IsNullOrEmpty(error))
            {
                LoadCustomer();
            }

            this.planNo.Leave += planNo_Leave;
            this.compID.Leave += compID_Leave;
            this.quantityValue.Leave += quantityValue_Leave;

            if (!string.IsNullOrEmpty(error))
            {
                MessageBox.Show(error);
            }
            
        }

        ///////////////////////////////////////
        /// Quantity Change
        ///////////////////////////////////////
        void quantityValue_Leave(object sender, EventArgs e)
        {
            QuantityChange();
        }

        private void QuantityChange()
        {
            if (!string.IsNullOrEmpty(error_quantity))
            {
                error = null;
            }
            int quantity = 0;
            error_quantity = null;
            if (string.IsNullOrEmpty(quantityValue.Text.Trim()))
            {
                error_quantity = "Please enter quantity value";
            }
            else
            {
                if (Int32.TryParse(quantityValue.Text.Trim(), out quantity))
                {
                    string comp = null;
                    ComboboxItem item = (ComboboxItem)this.compID.SelectedItem;
                    comp = item.Value.ToString();

                    if (comp.Substring(0, 3).ToUpper() == "GHR")
                    {
                        this.lastSerial.Text = firstLetter + GHRYearLetter + GetGHRSerialLetter(serialDigit + quantity)
                            + ((serialDigit + quantity) % 1000).ToString("D3");
                        this.buttonAdd.Enabled = true;
                    }
                    else
                    {
                        string[] array1 = { "RT-AF", "RT-AR", "RT-BF", "RT-BR", "RT-CF", "RT-CR" };
                        int NewSerialDigit;
                        if (array1.Contains(firstLetter))
                        {
                            NewSerialDigit = serialDigit + 1;
                            this.firstSerial.Text = firstLetter + "-" + "1" + yearLetter.Substring(yearLetter.Length - 1, 1) + "-"
                                + monthLetter + "-" + (NewSerialDigit % 100).ToString("D2");
                            NewSerialDigit = serialDigit + quantity;
                            this.lastSerial.Text = firstLetter + "-" + "1" + yearLetter.Substring(yearLetter.Length - 1, 1)
                                + "-" + monthLetter + "-" + (NewSerialDigit % 100).ToString("D2");
                        }
                        else
                        {
                            if (firstLetter == "RT")
                            {
                                Dictionary<string, string> month_letter = new Dictionary<string, string>()
                                    {{"01", "1"}, {"02", "2"}, {"03", "3"}, {"04", "4"}, {"05", "5"}, {"06", "6"},
                                    {"07", "7"}, {"08", "8"}, {"09", "9"}, {"10", "X"}, {"11", "Y"}, {"12", "Z"}};

                                Dictionary<string, string> year_letter = new Dictionary<string, string>()
                                    {{"A", "01"}, {"B", "02"}, {"C", "03"}, {"D", "04"}, {"E", "05"}, {"F", "06"}, {"G", "07"},
                                    {"H", "08"}, {"J", "09"}, {"K", "10"}, {"L", "11"}, {"M", "12"}, {"N", "13"}, {"O", "14"},
                                    {"P", "15"}};
                                
                                if (month_letter.ContainsKey(monthDigit))
                                {
                                    monthLetter = month_letter[monthDigit];
                                }

                                if (year_letter.ContainsKey(this.planNo.Text.Substring(0, 1)))
                                {
                                    yearLetter = year_letter[this.planNo.Text.Substring(0, 1)];
                                }
                                else
                                {
                                    yearLetter = "16";
                                }


                                NewSerialDigit = serialDigit + 1;
                                this.firstSerial.Text = firstLetter + yearLetter + monthLetter + (NewSerialDigit % 100).ToString("D2");
                                NewSerialDigit = serialDigit + quantity;
                                this.lastSerial.Text = firstLetter + yearLetter + monthLetter + (NewSerialDigit % 100).ToString("D2");
                            }
                            else
                            {
                                NewSerialDigit = serialDigit + 1;
                                serialLetter = GetSerialLetter(NewSerialDigit);
                                this.firstSerial.Text = firstLetter + yearLetter + monthLetter + serialLetter
                                    + (NewSerialDigit % 100).ToString("D2");
                                NewSerialDigit = serialDigit + quantity;
                                serialLetter = GetSerialLetter(NewSerialDigit);
                                this.lastSerial.Text = firstLetter + yearLetter + monthLetter + serialLetter + (NewSerialDigit % 100).ToString("D2");
                            }
                        }

                        this.buttonAdd.Enabled = true;
                    }
                }
                else
                {
                    error_quantity = "Invalid quantity value";
                }
            }

            if (!string.IsNullOrEmpty(error_quantity))
            {
                error = error_quantity;
            }
        }

        //////////////////////////////////////////////////
        /// Comp Chage
        //////////////////////////////////////////////////

        void compID_Leave(object sender, EventArgs e)
        {
            CompChange();
            if (string.IsNullOrEmpty(this.quantityValue.Text.Trim()))
            {
                QuantityChange();
            }
        }

        private void CompChange()
        {           
            if (this.compID.SelectedIndex < 0)
            {
                error = "Please select Comp";
            }

            if (string.IsNullOrEmpty(error))
            {
                string comp = null;
                ComboboxItem item = (ComboboxItem)this.compID.SelectedItem;
                comp = item.Value.ToString();

                if (comp.Substring(0, 3).ToUpper() == "GHR")
                {
                    firstLetter = "V";
                    int year = 0;
                    int month = 0;
                    if ((Int32.TryParse(yearLetter.Substring(1, 1), out year)) && (Int32.TryParse(monthDigit, out month)))
                    {
                        GHRYearLetter = "1" + (year + ((month + 9)/12) - 1).ToString();

                        try
                        {
                            myConnection.Open();
                            SqlCommand cmd_sql = myConnection.CreateCommand();
                            cmd_sql.CommandText = "SELECT TOP (10) CompID, SerialNo"
                                + " FROM [231_RobotOrderDetail] WHERE (LEFT(CompID, 5) LIKE N'" + comp.Substring(0, 5).ToUpper() + "')"
                                + " ORDER BY SUBSTRING(SerialNo, 4, 4) DESC";
                            SqlDataReader dataReader = cmd_sql.ExecuteReader();
                            if (dataReader.Read())
                            {
                                this.lastSerial.Text = dataReader["SerialNo"].ToString();
                            }
                            else
                            {
                                this.lastSerial.Text = firstLetter + GHRYearLetter + "0000";
                            }
                            dataReader.Close();
                            cmd_sql.Dispose();
                            myConnection.Close();
                        }
                        catch (Exception ex)
                        {
                            if (myConnection.State == ConnectionState.Open)
                            {
                                myConnection.Close();
                            }
                            MessageBox.Show(ex.Message);
                            //error = "Cannot get Comp list";
                        }

                        serialDigit = GetGHRSerialDigit(this.lastSerial.Text);

                        this.firstSerial.Text = firstLetter + GHRYearLetter + GetGHRSerialLetter(serialDigit + 1)
                            + ((serialDigit + 1) % 1000).ToString("D3");
                    }
                }

                try
                {
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "SELECT SerialNo FROM [222_Comp] WHERE CompID='" + comp + "'";
                    SqlDataReader dataReader = cmd_sql.ExecuteReader();
                    if (dataReader.Read())
                    {                        
                        firstLetter = dataReader["SerialNo"].ToString();
                    }                    
                    dataReader.Close();
                    cmd_sql.Dispose();
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    //error = "Cannot get Comp list";
                }

                string[] array1 = { "A", "B", "D", "E", "F", "H", "I", "J", "K", "L", "M", "N", "O", "P",
                                      "Q", "R", "S", "T", "U", "W", "Y", "Z" };
                string[] array2 = { "VQ", "VW", "VY", "VR", "VS", "VU", "VH", "VE", "AP", "AN", "AU" };
                string[] array3 = { "RT-AF", "RT-AR", "RT-BF", "RT-BR", "RT-CF", "RT-CR" };
                string[] array4 = { "RT" };

                if (array1.Contains(firstLetter))
                {
                    try
                    {
                        myConnection.Open();
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT CompID, SerialNo"
                            + " FROM [231_RobotOrderDetail]"
                            + " WHERE (LEFT(SerialNo, 1)='" + firstLetter + "')"
                            + " AND (SUBSTRING(SerialNo, 2, 2) = '" + yearLetter + "')"
                            + " AND (SUBSTRING(SerialNo, 4, 1) = '" + monthLetter + "')"
                            + " ORDER BY SerialNo DESC";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        if (dataReader.Read())
                        {
                            lastSerial.Text = dataReader["SerialNo"].ToString();
                            
                            if (lastSerial.Text.Substring(lastSerial.Text.Length - 1, 1) == "f")
                            {
                                lastSerial.Text = lastSerial.Text.Substring(0, 7);
                            } 
                        }
                        else
                        {
                            lastSerial.Text = firstLetter + yearLetter + monthLetter + "000";
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                        myConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show(ex.Message);
                        //error = "Cannot get Comp list";
                    }

                    serialDigit = GetSerialDigit(lastSerial.Text, 1);
                }

                if (array2.Contains(firstLetter))
                {
                    try
                    {
                        myConnection.Open();
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT CompID, SerialNo"
                            + " FROM [231_RobotOrderDetail]"
                            + " WHERE (LEFT(SerialNo, 2)='" + firstLetter + "')"
                            + " AND (SUBSTRING(SerialNo, 3, 2) = '" + yearLetter + "')"
                            + " AND (SUBSTRING(SerialNo, 5, 1) = '" + monthLetter + "')"
                            + " ORDER BY SerialNo DESC";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        if (dataReader.Read())
                        {
                            lastSerial.Text = dataReader["SerialNo"].ToString();
                        }
                        else
                        {
                            lastSerial.Text = firstLetter + yearLetter + monthLetter + "000";
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                        myConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show(ex.Message);
                        //error = "Cannot get Comp list";
                    }

                    serialDigit = GetSerialDigit(lastSerial.Text, 2);
                }

                if (array3.Contains(firstLetter))
                {
                    try
                    {
                        myConnection.Open();
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT CompID, SerialNo"
                            + " FROM [231_RobotOrderDetail]"
                            + " WHERE (LEFT(SerialNo, 2)='" + firstLetter.Substring(0, 2) + "')"
                            + " AND (SUBSTRING(SerialNo, 7, 1) = '" + yearLetter.Substring(-1, 1) + "')"
                            + " AND (SUBSTRING(SerialNo, 9,1) = '" + monthLetter + "')"
                            + " ORDER BY RIGHT(SerialNo, 6) DESC";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        if (dataReader.Read())
                        {
                            lastSerial.Text = dataReader["SerialNo"].ToString();                            
                        }
                        else
                        {
                            lastSerial.Text = firstLetter + "-" + yearLetter.Substring(yearLetter.Length - 1, 1)
                                + "-" + monthLetter + "-" + "00";
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                        myConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show(ex.Message);
                        //error = "Cannot get Comp list";
                    }

                    serialDigit = GetSerialDigit(lastSerial.Text, 3);
                }

                if (array4.Contains(firstLetter))
                {
                    Dictionary<string, string> month_letter = new Dictionary<string, string>()
                        {{"01", "1"}, {"02", "2"}, {"03", "3"}, {"04", "4"}, {"05", "5"}, {"06", "6"}, {"07", "7"},
                        {"08", "8"}, {"09", "9"}, {"10", "X"}, {"11", "Y"}, {"12", "Z"}};

                    Dictionary<string, string> year_letter = new Dictionary<string, string>()
                        {{"A", "01"}, {"B", "02"}, {"C", "03"}, {"D", "04"}, {"E", "05"}, {"F", "06"}, {"G", "07"},
                        {"H", "08"}, {"J", "09"}, {"K", "10"}, {"L", "11"}, {"M", "12"}, {"N", "13"}, {"O", "14"},
                        {"P", "15"}};

                    if (month_letter.ContainsKey(monthDigit))
                    {
                        monthLetter = month_letter[monthDigit];
                    }

                    if (year_letter.ContainsKey(this.planNo.Text.Substring(0, 1)))
                    {
                        yearLetter = year_letter[this.planNo.Text.Substring(0, 1)];
                    }
                    else
                    {
                        yearLetter = "16";
                    }                    

                    try
                    {
                        myConnection.Open();
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT CompID, SerialNo"
                            + " FROM [231_RobotOrderDetail]"
                            + " WHERE (LEFT(SerialNo, 2)='" + firstLetter.Substring(0, 2) + "')"
                            + " AND (SUBSTRING(SerialNo, 3, 2) = '" + yearLetter.Substring(-1, 2) + "')"
                            + " ORDER BY SerialNo DESC";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        if (dataReader.Read())
                        {
                            lastSerial.Text = dataReader["SerialNo"].ToString();
                        }
                        else
                        {
                            lastSerial.Text = firstLetter + yearLetter + monthLetter + "00";
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                        myConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show(ex.Message);
                        //error = "Cannot get Comp list";
                    }

                    serialDigit = GetSerialDigit(lastSerial.Text, 4);
                }
            }
        }

        ///////////////////////////////////////////
        /// Get Serial Letter
        ///////////////////////////////////////////
        private string GetSerialLetter(int digit)
        {
            string ret = null;
            //MessageBox.Show("Digit: " + digit);
            if (digit < 2000)
            {
                return digit.ToString("D4").Substring(1, 1);
            }

            if (digit < 2100)
            {
                return "A";
            }

            if (digit < 2200)
            {
                return "B";
            }

            if (digit < 2300)
            {
                return "C";
            }

            if (digit < 2400)
            {
                return "D";
            }

            if (digit < 2500)
            {
                return "E";
            }

            if (digit < 2600)
            {
                return "F";
            }

            if (digit < 2700)
            {
                return "G";
            }

            if (digit < 2800)
            {
                return "H";
            }

            if (digit < 2900)
            {
                return "J";
            }

            if (digit < 3000)
            {
                return "K";
            }

            if (digit < 3100)
            {
                return "L";
            }

            if (digit < 3200)
            {
                return "M";
            }

            if (digit < 3300)
            {
                return "N";
            }

            if (digit < 3400)
            {
                return "P";
            }

            if (digit < 3500)
            {
                return "Q";
            }

            if (digit < 3600)
            {
                return "R";
            }

            if (digit < 3700)
            {
                return "S";
            }

            if (digit < 3800)
            {
                return "T";
            }

            if (digit < 3900)
            {
                return "U";
            }

            if (digit < 4000)
            {
                return "V";
            }

            if (digit < 4100)
            {
                return "W";
            }

            if (digit < 4200)
            {
                return "X";
            }

            if (digit < 4300)
            {
                return "Y";
            }

            if (digit < 4400)
            {
                return "Z";
            }

            return ret;
        }


        ///////////////////////////////////////////
        /// Get Serial Digit
        //////////////////////////////////////////
        private int GetSerialDigit(string last_Serial, int mode)
        {
            int ret = 0;
            int i = 0;
            if ((mode == 2) || (mode == 3))
            {
                i = 6;
            }
            else
            {
                i = 5;
            }
            string str = last_Serial.Substring(i - 1, 1);

            Dictionary<string, int> dict = new Dictionary<string, int>()
                {{"A", 2000}, {"B", 2100}, {"C", 2200}, {"D", 2300}, {"E", 2400}, {"F", 2500}, {"G", 2600}, {"H", 2700},
                {"J", 2800}, {"K", 2900}, {"L", 3000}, {"M", 3100}, {"N", 3200}, {"P", 3300}, {"Q", 3400}, {"R", 3500},
                {"S", 3600}, {"T", 3700}, {"U", 3800}, {"V", 3900}, {"W", 4000}, {"X", 4100}, {"Y", 4200}, {"Z", 4300}};

            if (dict.ContainsKey(str))
            {
                ret = dict[str] + Int32.Parse(last_Serial.Substring(last_Serial.Length - 2, 2));
            }
            else
            {
                switch (mode)
                {
                    case 1:
                        ret = 1000 + Int32.Parse(last_Serial.Substring(4, 3));
                        break;
                    case 2:
                        ret = 1000 + Int32.Parse(last_Serial.Substring(5, 3));
                        break;
                    default:
                        ret = 1000 + Int32.Parse(last_Serial.Substring(last_Serial.Length - 2, 2));
                        break;
                }
            }
            
            return ret;
        }

        ///////////////////////////////////////////
        /// Get GHR Serial Digit
        //////////////////////////////////////////
        private int GetGHRSerialDigit(string GHRSerial)
        {
            int ret = 0;
            if (GHRSerial.Length == 8)
            {
                if (GHRSerial.Substring(7, 1).ToUpper() == "F")
                {
                    GHRSerial = GHRSerial.Substring(0, 7);
                }
            }

            string temp = GHRSerial.Substring(3, 4);
            if (Int32.TryParse(temp, out ret))
            {
                return ret;
            }
            else
            {
                ret = (Array.IndexOf(Alphabet, temp.Substring(0, 1)) + 10) * 1000 + Int32.Parse(temp.Substring(1,3));
            }
            return ret;
        }

        ///////////////////////////////////////////
        /// Get GHR Serial Letter
        //////////////////////////////////////////
        private string GetGHRSerialLetter(int SerialDigit)
        {
            string ret = "0";
            if (SerialDigit > 33000)
            {
                ret = "Z";
            }
            else
            {
                if (SerialDigit < 10000)
                {
                    ret = SerialDigit.ToString("D4").Substring(0, 1);
                }
                else
                {
                    int i = SerialDigit / 1000;
                    ret = Alphabet[i - 10];
                }
            }
            return ret;
        }



        ///////////////////////////////////////
        /// Plan No Change
        ///////////////////////////////////////
        void planNo_Leave(object sender, EventArgs e)
        {
            PlanChange();
            if ((this.compID.SelectedIndex >= 0) && (!string.IsNullOrEmpty(this.quantityValue.Text.Trim())))
            {
                CompChange();
                QuantityChange();
            }
        }

        private void PlanChange()
        {
            if (!string.IsNullOrEmpty(error_planno))
            {
                error = null;
            }
            
            error_planno = null;

            if (this.planNo.Text.Length != 9)
            {
                error_planno = "Invalid Plan No";
            }

            if (string.IsNullOrEmpty(error_planno))
            {
                monthDigit = this.planNo.Text.Substring(7, 2);
                int moth;
                if (!Int32.TryParse(monthDigit, out moth))
                {
                    error_planno = "Invalid Plan No";
                }
            }

            if (string.IsNullOrEmpty(error_planno))
            {
                Dictionary<string, string> year_letter = new Dictionary<string, string>()
                    {{"A", "A0"}, {"B", "A1"}, {"C", "A3"}, {"D", "A4"}, {"E", "A5"}, {"F", "A6"}, {"G", "A7"}, {"H", "A8"},
                    {"J", "A9"}, {"K", "B0"}, {"L", "B1"}, {"M", "B2"}, {"N", "B3"}, {"O", "B4"}, {"P", "B5"}};

                Dictionary<string, string> year_digit = new Dictionary<string, string>()
                    {{"A", "01"}, {"B", "02"}, {"C", "03"}, {"D", "04"}, {"E", "05"}, {"F", "06"}, {"G", "07"}, {"H", "08"},
                    {"J", "09"}, {"K", "10"}, {"L", "11"}, {"M", "12"}, {"N", "13"}, {"O", "14"}, {"P", "15"}};

                if (year_letter.ContainsKey(this.planNo.Text.Substring(0, 1)))
                {
                    yearLetter = year_letter[this.planNo.Text.Substring(0, 1)];
                }
                else
                {
                    yearLetter = "B6";
                }

                if (year_digit.ContainsKey(this.planNo.Text.Substring(0, 1)))
                {
                    yearDigit = year_digit[this.planNo.Text.Substring(0, 1)];
                }
                else
                {
                    yearDigit = "16";
                }

                Dictionary<string, string> month_letter = new Dictionary<string, string>()
                    {{"01", "A"}, {"02", "B"}, {"03", "C"}, {"04", "D"}, {"05", "E"}, {"06", "F"}, {"07", "G"}, {"08", "H"},
                    {"09", "J"}, {"10", "K"}, {"11", "L"}};

                if (month_letter.ContainsKey(monthDigit))
                {
                    monthLetter = month_letter[monthDigit];
                }
                else
                {
                    monthLetter = "M";
                }                
            }

            if (!string.IsNullOrEmpty(error_planno))
            {
                error = error_planno;
            }
        }

        ////////////////////////////////////////////
        /// Load Comp List
        ////////////////////////////////////////////
        private void LoadComp()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT CompID FROM [222_Comp] ORDER BY CompID";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["CompID"].ToString();
                    item.Value = dataReader["CompID"].ToString();
                    this.compID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                error = "Cannot get Comp list";
            }
        }

        ////////////////////////////////////////////
        /// Load Customer List
        ////////////////////////////////////////////
        private void LoadCustomer()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT CustomerID, FullName FROM [222_Customer] ORDER BY FullName";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["CustomerID"].ToString() + " | " + dataReader["FullName"].ToString() + ")";
                    item.Value = dataReader["CustomerID"].ToString();
                    this.customerID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                error = "Cannot get Customer list";
            }
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(error_planno) && string.IsNullOrEmpty(error_order) && string.IsNullOrEmpty(error_quantity))
            {
                error = null;
            }
            string order = null;
            string plan = null;
            string comp = null;
            int quantity = 0;
            DateTime deadline = new DateTime();
            string customer = null;
            string remark = null;
            string first_serial = null;
            string last_serial = null;           

            ComboboxItem item = new ComboboxItem();

            Dictionary<string, string> year_letter = new Dictionary<string, string>()
                {{"A", "01"}, {"B", "02"}, {"C", "03"}, {"D", "04"}, {"E", "05"}, {"F", "06"}, {"G", "07"}, {"H", "08"},
                {"J", "09"}, {"K", "10"}, {"L", "11"}, {"M", "12"}, {"N", "13"}, {"O", "14"}, {"P", "15"}};

            if (string.IsNullOrEmpty(this.planNo.Text))
            {
                error = "Please enter Plan No";
            }

            if (string.IsNullOrEmpty(error))
            {
                plan = this.planNo.Text.Substring(0, 7);

                if (year_letter.ContainsKey(plan.Substring(0, 1)))
                {
                    yearLetter = year_letter[plan.Substring(0, 1)];
                }
                else
                {
                    yearLetter = "16";
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(this.orderNo.Text))
                {
                    error = "Please enter Order No";
                }
                else
                {
                    order = this.orderNo.Text;
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.compID.SelectedIndex < 0)
                {
                    error = "Please select Comp ID";
                }
                else
                {
                    item = (ComboboxItem)this.compID.SelectedItem;
                    comp = item.Value.ToString();
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(this.quantityValue.Text))
                {
                    error = "Please enter Quantity";
                }
                else
                {
                    if (!Int32.TryParse(this.quantityValue.Text, out quantity))
                    {
                        error = "Invalid Quantity";
                    }
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.customerID.SelectedIndex >= 0)
                {
                    item = (ComboboxItem)this.customerID.SelectedItem;
                    customer = item.Value.ToString();
                }
            }

            if (this.deadLine.EditValue == null)
            {
                error = "Please select DeadLine";
            }

            if (string.IsNullOrEmpty(error))
            {
                deadline = DateTime.Parse(this.deadLine.EditValue.ToString());

                if (deadline.Year.ToString().Substring(2, 2) != yearLetter)
                {
                    error = "Plan No and DeadLine is not compatible";
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                remark = this.remarkText.Text;
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.pbFree.Checked)
                {
                    first_serial = this.firstSerial.Text + "f";
                    last_serial = this.lastSerial.Text + "f";
                }
                else
                {
                    first_serial = this.firstSerial.Text;
                    last_serial = this.lastSerial.Text;
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (comp.Substring(0, 3).ToUpper() == "GHR")
                {
                    for (int i = 1; i <= quantity; i++)
                    {
                        string serial = firstLetter + GHRYearLetter + GetGHRSerialLetter(serialDigit + i)
                            + ((serialDigit + i) % 1000).ToString("D3") + (this.pbFree.Checked ? "f" : "");

                        //Insert into 231_RobotOrderDetail
                        try
                        {                            
                            myConnection.Open();
                            string sql = "INSERT INTO [231_RobotOrderDetail]"
                                + " (SerialNo, PlanNo, OrderNo, CompID, Quantity, CustomerID, Remark)"
                                + " VALUES ('" + serial + "', '" + plan + "', '" + order + "', '" + comp + "',"
                                + " " + quantity + ", '" + customer + "', '" + remark + "')";
                            SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                            cmd_sql.ExecuteNonQuery();                           
                            cmd_sql.Dispose();
                            myConnection.Close();                            
                        }
                        catch //(Exception ex)
                        {
                            if (myConnection.State == ConnectionState.Open)
                            {
                                myConnection.Close();
                            }
                            //MessageBox.Show(ex.Message);                            
                        }

                    } // End for                    
                }
                else // Comp != GHR
                {
                    if (!this.pbFree.Checked)
                    {
                        int NewSerialDigit = 0;
                        string[] firstletter_array = { "RT-AF", "RT-AR", "RT-BF", "RT-BR", "RT-CF", "RT-CR" };
                        string serial = null;

                        for (int i = 1; i <= quantity; i++)
                        {
                            NewSerialDigit = serialDigit + i;
                            serialLetter = GetSerialLetter(NewSerialDigit);

                            if (firstletter_array.Contains(firstLetter))
                            {
                                serial = firstLetter + "-" + yearLetter.Substring(yearLetter.Length - 1, 1)
                                    + "-" + monthLetter + "-" + (NewSerialDigit % 100).ToString("D2");
                            }
                            else
                            {
                                if (firstLetter == "RT")
                                {
                                    serial = firstLetter + yearLetter + monthLetter + (NewSerialDigit % 100).ToString("D2");
                                }
                                else
                                {
                                    serial = firstLetter + yearLetter + monthLetter + serialLetter + (NewSerialDigit % 100).ToString("D2");
                                }
                            }

                            //Insert into 231_RobotOrderDetail
                            try
                            {
                                myConnection.Open();
                                string sql = "INSERT INTO [231_RobotOrderDetail]"
                                    + " (SerialNo, PlanNo, OrderNo, CompID, Quantity, CustomerID, Remark)"
                                    + " VALUES ('" + serial + "', '" + plan + "', '" + order + "', '" + comp + "',"
                                    + " " + quantity + ", '" + customer + "', '" + remark + "')";
                                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                                cmd_sql.ExecuteNonQuery();
                                cmd_sql.Dispose();
                                myConnection.Close();
                            }
                            catch //(Exception ex)
                            {
                                if (myConnection.State == ConnectionState.Open)
                                {
                                    myConnection.Close();
                                }
                                //MessageBox.Show(ex.Message);                            
                            }

                        } // End for                        
                    }
                    else // PBFree checked
                    {
                        int NewSerialDigit = 0;                        
                        string serial = null;

                        for (int i = 1; i <= quantity; i++)
                        {
                            NewSerialDigit = serialDigit + i;
                            serialLetter = GetSerialLetter(NewSerialDigit);

                            serial = firstLetter + yearLetter + monthLetter + serialLetter
                                + (NewSerialDigit % 100).ToString("D2") + "f";                            

                            //Insert into 231_RobotOrderDetail
                            try
                            {
                                myConnection.Open();
                                string sql = "INSERT INTO [231_RobotOrderDetail]"
                                    + " (SerialNo, PlanNo, OrderNo, CompID, Quantity, CustomerID, Remark)"
                                    + " VALUES ('" + serial + "', '" + plan + "', '" + order + "', '" + comp + "',"
                                    + " " + quantity + ", '" + customer + "', '" + remark + "')";
                                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                                cmd_sql.ExecuteNonQuery();
                                cmd_sql.Dispose();
                                myConnection.Close();
                            }
                            catch //(Exception ex)
                            {
                                if (myConnection.State == ConnectionState.Open)
                                {
                                    myConnection.Close();
                                }
                                //MessageBox.Show(ex.Message);                            
                            }
                        } //End for                        
                    }
                }

                // Insert into 231_TimePlan
                try
                {
                    myConnection.Open();
                    string sql = "INSERT INTO [231_TimePlan]"
                        + " (TimePlanNo, PlanNo, CompID, Quantity, CustomerID, FromSerialNo, ToSerialNo, DeadLine, Remark)"
                        + " VALUES ('" + order + "', '" + plan + "', '" + comp + "', " + quantity + ","
                        + " '" + customer + "', '" + first_serial + "', '" + last_serial + "', @deadline, '" + remark + "')";
                    SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                    cmd_sql.Parameters.Add("@deadline", SqlDbType.DateTime).Value = deadline.Date;

                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.Dispose();
                    myConnection.Close();
                    MessageBox.Show("F4 Serial Input success.");
                }
                catch //(Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    //MessageBox.Show(ex.Message);
                    MessageBox.Show("Cannot add F4 Serial. Please try again.");
                }
            }
            else
            {
                MessageBox.Show(error);
            }            
        } 
    }
}
